Back to Main Menu

Create Assessment Form from csv (Assetic Python SDK)

Introduction

The Assetic Python SDK allows a new Assessment form to be created using a csv file to define the form.

 

There are 2 aspects to the form creation:

  1. Defining the fields in the form via a csv file.

  2. Setting basic form attributes such as form name, description, form level etc. This step has been simplified by a script that prompts you for the required information. See the Quick Start below.

 

Refer also to the article Manage Custom Assessment Forms for additional information about assessment forms and how they are created using the Assetic application itself.

Quick Start

NOTE  Make sure your Assetic Python SDK version is 2017.13.1.0 or greater.  Refer to the article Assetic Python SDK Quick Start for details on how to install or upgrade the Assetic Python SDK.

First create a csv file (using Excel or other editor) or Download this sample here: (FormFields.csv)

 

Contains parent/child comboxbox (FormFieldsParentChild.csv)

 

(Samples may open in the browser rather than downloading.  Right-click in the browser and choose the 'Save As' option)

 

Data Sample:

The following csv data sample shows a typical structure for creating an assessment form.  It is shown in a grid to make each column more obvious.

 

The column structure of this csv data is Label, Type, Attributes, Widget, Required, Hint

Operating Facility   100 Facility Detail True The facility name
Primary Location   100 Facility Detail True Main Location
System Dropdown 1|Type A;2|Type B System Detail True  
Classification     System Detail    
Installed Area NumericStepper 0|100|1|n0 System Detail    
Complexity of Access Dropdown Low|Low;Medium|Medium;High|High Facility Detail    
Comment MultiLineText 1000 System Detail   As per standard AC:3423
Is Active Checkbox   Facility Detail    
Tagged Service Date Date   System Detail    
Running Since DateTime   System Detail   The date and time the unit was last started
Location Map   Facility Detail    

 

Some notes about the above sample:

  • Only 1 column is mandatory, the "Label" column. All other columns are optional, and the order of the columns is not fixed.

  • 'Facility Detail' and 'System Detail' are the labels for the 2 control groups that will contain the form fields. The form will therefore need to be created with a layout containing at least 2 control groups

  • Where the form field "Type" column is empty, the default will be TextBox.

  • Default values are applied if the "Attributes" cell is empty

  • The format of the contents of the "Attributes" cell varies depending on the form field type

  • The default value for 'Required' is False (i.e. the field is not mandatory)

Form Creation

Having created the csv file, the next step is to create the form.

Hint: You may need to right click and 'Save As' (Assetic.AssessmentFormCreateByPrompt.py)

 

This script will prompt you for the form details and create the form. Once the form is created you will be prompted to launch Assetic. Assetic will launch and display the new form in the Assessments Admin module.

 

Defining Parent-Child Combobox Relationships

A parent-child combobox relationship can be defined such that the selection of a value in the parent combobox results in the filtering of child combobox values.

 

To define these relationships 2 additional columns are added to the csv file. The columns are only populated for the child combobox definition row in the the csv:

  • ParentLabel

    • The 'label' of the parent combox to associate the child combobx to

  • ParentRelation

    • This is the relationship of the child combobox values to the parent comboxbox values and is by its nature complex to define.

    • The definition requires each child combobox value to be linked to values from the parent combobox.

      • A pair of carets ^^ to delimit each child combobox link setting

      • Within each child combobox link setting the parent combobox values are delimited by a semicolon. A pipe is used to delimit the child combobox value from the subsequent list of parent combobox values

 

The following example from the sample file FormFieldsParentChild.csv illustrates the configuration.

 

Label Type Attributes ParentRelation ParentLabel
System Dropdown 1|Type A;2|Type B;3|All    
Sub System Dropdown 1|Sub A1;2|Sub A2;3|Sub B1;4|Sub B2 1|1;3^^2|1;3^^3|2;3^^4|2;3 System

 

Based on the above configuration the following outcome is expected:

Parent Selection Child Selection Options
Type A Sub A1, Sub A2
Type B Sub B1, Sub B2
All Sub A1, Sub A2, Sub B1, Sub B2

 

Advanced Options and Field Descriptions

The Assetic Python SDK supports form creation without having to use the script AssessmentFormCreateByPrompt.py. The following sections describe in detail the structure of the field types and how to create a form without using the script from the Quick Start section above.

1. Form Attributes

The python object assetic.AssessmentFormCreateRepresentation is used to define the form attributes required by the Assessments API.

 

The following attributes of an assessment form can be set via this representation:

Attribute

Name

Description Mandatory Default
form_name The name of the form to create.  Must be unique.  This will not be user visible, but this is the reference name used when using the Assetic Data Exchange API for bulk importing Assessment Form Results (Data Exchange Integration) Yes -
form_label  A user friendly label for the form that is shown when selecting the form in Data Exchange and advanced search Yes -
form_level Defines the module that the from targets.  One of: "Asset", "GroupAsset", "ComplexAsset","Component", "ComponentServiceCriteria","NetworkEntity","SimpleAsset", "WorkOrder", "WorkRequest", "WorkTask","AssesmentsResult", "Documents", "AsmtProject", "AsmtTask", "ServiceCriteria" Yes -
version A version number to assign to the form No 1
can_add_attachment Allows document attachments to be linked to the form.  Boolean True/False No True
tab_label The label of the tab in the form  No "Tab"
layout_pattern  Defines the pattern of the form layout.  Options include but not limited to: "Single", "column: 2 equal columns" No  "Single"
widget_labels

For the given layout, each group of controls has a label.  This is an array.

"Single" has 1 group of controls, "column: 2 equal columns" has 2 groups of controls so 2 labels are required

No "Data"
label_formatter Use a custom method to reformat the label as it appears in the csv file.  Examples include replacing "_" with " ", or converting upper case to 'proper case' so that the labels are more user friendly.  Useful where an external application is generating the csv file. No -

 

2. Form Labels - CSV file structure

A csv file is used to define the list of fields in a form.

NOTE  this file should not have a 'header' row that defines each column. The column structure is defined when executing the form creation.

 

The structure of the csv file follows a basic pattern whereby each row in the csv file represents a control in the form. A "control" is the fields in a form that data will be added to.

 

At a minimum the 'label' (user-visible label) of the control is required. The control type will default to 'TextBox' if only a list of labels is provided.

 

The controls available are:

Type Description Data Attributes Default Data  Settings
TextBox Standard single line text box Character limit 200 character limit
MultiLineText Text that may span multiple lines.  Use where there is a large amount of text Character limit 1000 character limit
Dropdown A dropdown list of items.  Can set values with user friendly descriptions Dropdown items, values with corresponding labels -
Date A datetime field that allows date to be selected by a calendar popup - -
DateTime A datetime field that allows date to be selected by a calendar popup and time to be selected by a time dropdown - -
NumericStepper

A numeric field with arrows to increase or decrease the number. The minimum value, maximum value, and value to increment the number by via the stepper may also be defined 

Minimum value, Maximum value, Step value, number format

min=0, max=100, step=1, format=n0

CheckBox A True/False style tick box - -
Map A map window - -

 

A column in the csv file that uses the same control type names as above can be used to set the control type for each label. It is permissible to use control names that vary from the above - refer to 'Non-standard control type names' below.

 

The following attributes for a control may be defined:

Attribute Type Description Format Default
Attributes The data attributes of the control as defined in the table above per control type.  This is a single column in the csv file that used for all the different control types.  The format will therefore change per control type.

For Dropdown separate the value from the label with "|", and separate each pair of items with ";".  Optionally use a separate column for dropdown items (format is unchanged)

For Numeric stepper separate min value, max value, step increment, and number format with "|".  Optionally use up to 4 separate columns in the file purely for stepper attributes ('minval", "maxval", "increment", "valueformat")  

For text and multiline text  define an integer value for field length.  Optionally use a separate column in the file purely for 'Length'

As above

Hint A 'mouse-over' hint (tooltip) to apply to the control. Text  No hint if undefined
Required Indicates the field is mandatory, and when viewing the form an asterisk will appear beside the label True/False False
Name The reference name for the control.  Use this if integrating to another system and the name is required for integration Text.  Cannot start with a number and should not have hyphens within the name.  Must be unique within the form A unique id will be generated for the name 
 Widget  The name of the form group widget to place the control in.  Required if a layout with more than one form group widget has been defined. Text.  Must match the name of one of the widgets from the form definition "widget_labels" -

 

3. Non-standard control type names

If another system has created the csv file, and the control type names vary from the above list, the control type name used in the csv file can be matched to one of the examples above using the "control_types" dictionary property of the SDK AssessmentHelper module.

assesshelp = assetic.AssessmentHelper(asseticsdk.client)     
assesshelp.control_types["MultiLineText"] = "Multi line Text" 
assesshelp.control_types["NumericStepper"] = "Numeric"

Code Sample

The following code sample is a complete sample for creating an assessment form from a csv file.  It uses the same csv data as in the sample above, except the names for the types "MultiLineText" and "NumericStepper" are different in the csv file (to illustrate how this is managed)

  • """ Example script to create an assessment form (Assetic.AssessmentFormCreate.py) Creates an assessment form in Assetic """ import assetic from assetic import Configuration from assetic import ApiClient from assetic import AssessmentTools import re #to replace camelcase with spaced label import csv #to read csv file with form definition import six # Assetic SDK instance. asseticsdk = assetic.AsseticSDK("c:/users/you/assetic.ini",None,"Info") def main(): fid=67 assesshelp = AssessmentHelper(asseticsdk.client) form_repr = AssessmentFormCreateRepresentation() form_repr.form_name = "ALPACA{0}".format(fid) form_repr.form_label = "Alpaca Test Form {0}".format(fid) form_repr.form_level = "Asset" form_repr.version = 1 form_repr.can_add_attachment = True form_repr.tab_label = "Data Tab" form_repr.layout_pattern = "Single" form_repr.layout_pattern = "column: 2 equal columns" widget_labels = list() widget_labels.append("Section 1") widget_labels.append("Section 2") form_repr.widget_labels = widget_labels form_repr.label_formatter = None assesshelp.control_types["MultiLineText"] = "Multi line Text" assesshelp.control_types["NumericStepper"] = "Numeric" csv_structure = None csvfile = "C:/temp/HVAC fields.csv" assesshelp.create_form_from_csv(form_repr,csvfile,csv_structure) class AssessmentHelper(object): """ Class to manage simplified processes for creating assessment forms such Uses the AssessmentTools class """ def __init__(self, api_client=None): config = Configuration() if api_client: self.api_client = api_client else: if not config.api_client: config.api_client = ApiClient() self.api_client = config.api_client self.logger = config.packagelogger self.assesstool = assetic.AssessmentTools() self._control_types = self.initiate_control_types() @property def control_types(self): """ dictionary of control type. The dictionary values can be changed to match the source data. The corresponding keys are tested for when determine which control to create """ return self._control_types @control_types.setter def control_types(self,control_types): self._control_types = control_types def create_form_from_csv(self,form_repr,csvfile,structure=None): """ create an assessment form from a csv file definition :param form_repr: A representation containing the core form creation details. AssessmentFormCreateRepresentation :param csvfile: The file containing the form definition :param structure: an optional mapping of row number in csv file to form creation purpose. e.g row 1 = control header, row 2 = control type :returns: >0=error, 0 = Success """ form_name = form_repr.form_name if form_repr.form_label == None: form_label = form_repr.form_name else: form_label = form_repr.form_label form_level = form_repr.form_level version = form_repr.version can_add_attachment = form_repr.can_add_attachment tab_label = form_repr.tab_label layout_pattern = form_repr.layout_pattern widget_labels = form_repr.widget_labels ##first create the form definition form = self.assesstool.new_form(form_name,form_level,form_label) ##create tab and widgets tab = self.assesstool.add_tab_with_widgets_to_form(form,tab_label, widget_labels,layout_pattern) if tab == None: ##error encountered return 1 ##get the structure from csv rows = self.read_csv_file_into_dict(csvfile,structure) if len(rows) == 1: return 1 ##Add the controls for row in rows: self.process_csv_dict_row(row,tab,widget_labels) ##now create form self.assesstool.create_form(form) def read_csv_file_into_dict(self,filename,structure=None): """ Read the the contents of a csv file into a representation that can be used to build the controls on a form :param filename: the file to read :param structure: the layout mapping of the rows in the file :returns: a list of the representation Assetic3IntegrationRepresentationsAssessmentFormFormControlRepresentation() """ if structure == None: structure = ("Label","Type","Attributes","Widget","Required") rows = list() if six.PY2: with open(filename, 'rt') as csvfile: readCSV = csv.DictReader( csvfile,fieldnames=structure,delimiter=',') for row in readCSV: rows.append(row) else: with open(filename, 'rt', encoding='utf-8', newline = '') as csvfile: readCSV = csv.DictReader(csvfile,fieldnames=structure, delimiter=',') for row in readCSV: rows.append(row) return rows def process_csv_dict_row(self,field_def,tab,widget_labels): """ The field definition is from csv.DictReader. Look for specific headers to determine what kind of control to create and it's attributes. :param field_def: the dictionary for a field :param tab: the tab to add the control to :param widget_labels: the widget to add the control to must be in the tab :returns: a list of the representation Assetic3IntegrationRepresentationsAssessmentFormFormControlRepresentation() """ label = None if "Label" in field_def: label = field_def["Label"] else: #assume first element is the label label = field_def[0] #check if hint for control but otherwise leave null hint = None if "Hint" in field_def: hint = field_def["Hint"] #check if name for control but otherwise leave null name = None if "Name" in field_def: name = field_def["Name"] #check if 'required' is set required = None if "Required" in field_def: required = field_def["Required"] #check if widget is defined. If not set as first label widget_label = widget_labels[0] if "Widget" in field_def and field_def["Widget"] in widget_labels: widget_label = field_def["Widget"] ##now test for type and create control if "Type" not in field_def: #assume text box field_def["Type"] = "textbox" if "Type" in field_def: if field_def["Type"].lower() == \ self._control_types["TextBox"].lower(): #Standard text box #get length length = 200 #Default field length(will be used if undefined) if "Length" in field_def: length = self.get_numeric_or_default( field_def["Length"],200) elif "Attributes" in field_def: length = self.get_numeric_or_default( field_def["Attributes"],200) control = self.assesstool.new_text_control(label,name,length, hint=hint,required=required) elif field_def["Type"].lower() == \ self._control_types["MultiLineText"].lower(): ##this is a multiline text box length = 1000 #Default field length(will be used if undefined) if "Length" in field_def: length = self.get_numeric_or_default( field_def["Length"],1000) elif "Attributes" in field_def: length = self.get_numeric_or_default( field_def["Attributes"],1000) control = self.assesstool.new_multiline_text_control(label, name,length,hint=hint,required=required) elif field_def["Type"].lower() == \ self._control_types["Dropdown"].lower(): ##this is a combobox (drop down list) #get items itemstring = None if "DropdownItems" in field_def: itemstring = field_def["DropdownItems"] elif "Attributes" in field_def: itemstring = field_def["Attributes"] item_delim=";" #hardcode delimiter for the moment key_value_delim="|" #hardcode delimiter for the moment items = self.get_combobox_items( itemstring,item_delim,key_value_delim) control = self.assesstool.new_combobox_control(label,name, items=items,hint=hint,required=required) elif field_def["Type"].lower() == \ self._control_types["Date"].lower(): ##this is a date picker control = self.assesstool.new_date_control(label,name, hint=hint,required=required) elif field_def["Type"].lower() == \ self._control_types["DateTime"].lower(): ##this is a date picker control = self.assesstool.new_datetime_control(label,name, hint=hint,required=required) elif field_def["Type"].lower() == \ self._control_types["NumericStepper"].\ lower(): ##this is a numeric stepper #get items sdef = self.get_stepper_defs(field_def) control = self.assesstool.new_stepper_control(label,name, sdef["minval"],sdef["maxval"],sdef["increment"], mask=sdef["valueformat"],hint=hint,required=required) elif field_def["Type"].lower() == \ self._control_types["CheckBox"].lower(): ##this is a checkbox control = self.assesstool.new_checkbox_control(label,name, hint=hint,required=required) elif field_def["Type"].lower() == \ self._control_types["Map"].lower(): ##this is a map control control = self.assesstool.new_map_control(label,name, hint=hint,required=required) else: ##Assume text #get length length = 200 #Default field length(will be used if undefined) if "Length" in field_def: length = self.get_numeric_or_default( field_def["Length"],200) elif "Attributes" in field_def: length = self.get_numeric_or_default( field_def["Attributes"],200) control = self.assesstool.new_text_control(label,name,length, hint=hint,required=required) #Add the control definition to the tab if control != None: self.assesstool.add_control_to_tab_widget(tab,widget_label, control) def get_combobox_items(self,itemstring,item_delim=";",key_value_delim="|"): """ given a string decode into a list of key/value pairs and create combobox items. By default key/value pairs are separated by ";" By default "|" for splitting key from value If only value supplied then key = value :param itemstring: the string containing key/value pairs :param item_delim: delimiter between each set of items (key/value pairs) Default = ";" :param key_value_delim:delimiter between key and value. default = "|" :returns: a list of combobox item representations Assetic3IntegrationRepresentationsAssessmentFormFormControlRepresentation """ items = list() if itemstring == None or itemstring.strip() == "": #no lookups items defined return items #split into key/value pairs pairs = itemstring.split(item_delim) for kvp in pairs: #split key and value itemkvp = kvp.split(key_value_delim) if len(itemkvp) == 1: #only label defined. Set key to label itemkvp = itemkvp + itemvkp #add item representation to items list items.append(self.assesstool.new_combobox_item( itemkvp[1],itemkvp[0])) return items def get_stepper_defs(self,field_def): """ given a form field configuration definition, test if there are settings for a numeric stepper :param field_def: the dictionary of settings to test :returns: a dictionary of the parameters and their value """ attributes_delim="|" #hardcode attributes delimiter for the moment default_min = 0 default_max = 0 default_inc = 0 default_format = "n0" stepperdict = dict() ##apply defaults and then override if there is a setting stepperdict["minval"] = default_min stepperdict["maxval"] = default_max stepperdict["increment"] = default_inc stepperdict["valueformat"] = default_format #First look for separately defined parameters useatts = True #flag to indicate use of generic attributes field if "MinValue" in field_def: stepperdict["minval"] = field_def["MinValue"] useatts = False if "MaxValue" in field_def: stepperdict["maxval"] = field_def["MaxValue"] useatts = False if "ValueIncrement" in field_def: stepperdict["increment"] = field_def["ValueIncrement"] useatts = False if "ValueFormat" in field_def: stepperdict["valueformat"] = field_def["ValueFormat"] #If useatts = True set then look for delimited 'Attributes' and split if useatts == True and "Attributes" in field_def and \ field_def["Attributes"] != None: stepperdefs = field_def["Attributes"].split(attributes_delim) stepperdict["minval"] = self. get_numeric_or_default( stepperdefs[0],default_min) if len(stepperdefs) > 1: stepperdict["maxval"] = self.get_numeric_or_default( stepperdefs[1],1000) if len(stepperdefs) > 2: stepperdict["increment"] = self.get_numeric_or_default( stepperdefs[2],1) if len(stepperdefs) > 3: stepperdict["valueformat"] = stepperdefs[3] return stepperdict def get_numeric_or_default(self,value,default): """ given a string, test if numeric and if not return default :param value: the string to test :param default: the default vaue.Must be a numeric (is not validated) :returns: the reformatted value as a numeric """ try: numeric = value/1 except: numeric = default return numeric def insert_space_in_camelcase(self,value): """ given a string, insert a space in from of upper case characters or numbers. Use to make a user presentable label :param value: the string to operate on :returns: the reformatted string """ return re.sub('([A-Z]{1})', r' \1',value).strip().strip("_") def initiate_control_types(self): """ initiate a dictionary of control type names with default values. :returns: dictionary of supported control types """ control_types = dict() control_types["TextBox"] = "TextBox" control_types["MultiLineText"] = "MultiLineText" control_types["Dropdown"] = "Dropdown" control_types["Date"] = "Date" control_types["DateTime"] = "DateTime" control_types["NumericStepper"] = "NumericStepper" control_types["CheckBox"] = "CheckBox" control_types["Map"] = "Map" return control_types class AssessmentFormCreateRepresentation(object): """" A structure for defining table metadata and relationships between search profile names, id's and tables """ def __init__(self,form_name=None,form_label=None,form_level=None,version=1,can_add_attachment=True,tab_label="Tab",layout_pattern="Single",widget_labels="Data",label_formatter=None): """ AssessmentFormCreateRepresentation - a model defining the core form details - form, tab, layoutpattern, control group labels """ self.fieldtypes = { "form_name": "str", "form_label": "str", "form_level": "str", "version": "int", "can_add_attachment": "bool", "tab_label":"string", "layout_pattern":"string", "widget_labels":"list[str]", "label_formatter": "string" } self._form_name = form_name self._form_label = form_label self._form_level = form_level self._version = version self._can_add_attachment = can_add_attachment self._tab_label = tab_label self._layout_pattern = layout_pattern if widget_labels == None: widget_labels = [] self._widget_labels = widget_labels self._label_formatter = label_formatter @property def form_name(self): return self._form_name @form_name.setter def form_name(self,form_name): self._form_name = form_name @property def form_label(self): return self._form_label @form_label.setter def form_label(self,form_label): self._form_label = form_label @property def form_level(self): return self._form_level @form_level.setter def form_level(self,form_level): self._form_level = form_level @property def version(self): return self._version @version.setter def version(self,version): self._version = version @property def can_add_attachment(self): return self._can_add_attachment @can_add_attachment.setter def can_add_attachment(self,can_add_attachment): self._can_add_attachment = can_add_attachment @property def tab_label(self): return self._tab_label @tab_label.setter def tab_label(self,tab_label): self._tab_label = tab_label @property def layout_pattern(self): return self._layout_pattern @layout_pattern.setter def layout_pattern(self,layout_pattern): self._layout_pattern = layout_pattern @property def widget_labels(self): return self._widget_labels @widget_labels.setter def widget_labels(self,widget_labels): self._widget_labels = widget_labels @property def label_formatter(self): return self._label_formatter @label_formatter.setter def label_formatter(self,label_formatter): self._label_formatter = label_formatter def to_dict(self): """ Returns the model properties as a dict """ result = {} for attr, _ in six.iteritems(self.fieldtypes): value = getattr(self, attr) if isinstance(value, list): result[attr] = list(map( lambda x: x.to_dict() if hasattr(x, "to_dict") else x, value )) elif hasattr(value, "to_dict"): result[attr] = value.to_dict() elif isinstance(value, dict): result[attr] = dict(map( lambda item: (item[0], item[1].to_dict()) if hasattr(item[1], "to_dict") else item, value.items() )) else: result[attr] = value return result def to_str(self): """ Returns the string representation of the model """ return pformat(self.to_dict()) def __repr__(self): """ For `print` and `pprint` """ return self.to_str() def __eq__(self, other): """ Returns true if both objects are equal """ return self.__dict__ == other.__dict__ def __ne__(self, other): """ Returns true if both objects are not equal """ return not self == other if __name__ == "__main__": main()